import pymysql
from pymysql.cursors import DictCursor
from backend.utils.db import get_db_connection
from datetime import datetime


class AfterSalesService:
    def get_after_sales_orders(self, user_id):
        try:
            # 修复：正确使用数据库连接上下文管理器
            with get_db_connection() as (cursor, conn):
                sql = """
                      SELECT o.order_id, p.name AS product_name, o.status
                      FROM orders o
                               JOIN products p ON o.product_id = p.product_id
                      WHERE o.user_id = %s \
                        AND o.status IN ('已收货', '已发货') \
                      """
                cursor.execute(sql, (user_id,))
                orders = cursor.fetchall()
                return orders
        except Exception as e:
            print(f"获取售后订单失败: {e}")
            import traceback
            traceback.print_exc()  # 添加详细错误追踪
            return []

    def get_user_orders_eligible_for_after_sales(self, user_id):
        """获取用户可申请售后的订单（已收货状态）"""
        try:
            with get_db_connection() as (cursor, conn):
                sql = """
                      SELECT o.user_id,
                             o.order_id,
                             p.name as product_name,
                             o.shipping_address,
                             o.status,
                             o.receive_time
                      FROM orders o
                               JOIN products p ON o.product_id = p.product_id
                      WHERE o.user_id = %s
                        AND o.status = '已收货'
                      ORDER BY o.receive_time DESC
                      """
                cursor.execute(sql, (user_id,))
                return cursor.fetchall()
        except Exception as e:
            print(f"获取可售后订单失败: {e}")
            return []

    def get_all_after_sales_orders(self):
        """获取所有售后相关订单（售后中和售后完成）"""
        try:
            with get_db_connection() as (cursor, conn):
                sql = """
                      SELECT o.order_id, 
                             o.user_id, 
                             p.name as product_name,
                             o.shipping_address, 
                             o.status, 
                             o.updated_at
                      FROM orders o
                               JOIN products p ON o.product_id = p.product_id
                      WHERE o.status IN ('售后中', '售后完成')
                      ORDER BY o.updated_at DESC 
                      """
                cursor.execute(sql)
                return cursor.fetchall()
        except Exception as e:
            print(f"获取所有售后订单失败: {e}")
            return []

    def create_after_sales_request(self, order_id, reason):
        """创建售后请求（退货/换货）"""
        try:
            with get_db_connection() as (cursor, conn):
                # 检查订单状态
                check_sql = "SELECT status, after_sales_status FROM orders WHERE order_id = %s"
                cursor.execute(check_sql, (order_id,))
                order = cursor.fetchone()

                if not order:
                    return False, "订单不存在"

                # 只有已收货的订单可以申请售后
                if order['status'] != '已收货' or order['after_sales_status'] != 'none':
                    return False, "只有已收货且未申请售后的订单可以申请售后"

                    # 创建售后记录
                sql = """
                      INSERT INTO after_sales_records
                          (order_id, reason, status, created_at)
                      VALUES (%s, %s, %s, %s) \
                      """
                created_at = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
                cursor.execute(sql, (order_id, reason, 'applied', created_at))

                # 更新订单状态为申请售后
                update_sql = """
                             UPDATE orders
                             SET after_sales_status = 'applied',
                                 updated_at         = %s
                             WHERE order_id = %s \
                             """
                cursor.execute(update_sql, (created_at, order_id))

                conn.commit()
                return True, "售后请求已提交"
        except Exception as e:
            conn.rollback()
            print(f"创建售后请求失败: {e}")
            return False, f"创建失败: {str(e)}"

        def process_after_sales_request(self, order_id, action):
            """处理售后请求（同意或拒绝）"""
            try:
                with get_db_connection() as (cursor, conn):
                    # 获取当前售后状态
                    cursor.execute("SELECT after_sales_status FROM orders WHERE order_id = %s", (order_id,))
                    current_status = cursor.fetchone()['after_sales_status']

                    if current_status != 'applied':
                        return False, "该订单没有待处理的售后申请"

                    updated_at = datetime.now().strftime('%Y-%m-%d %H:%M:%S')

                    if action == 'approve':
                        # 同意售后
                        new_status = 'approved'
                        order_status = '售后中'
                    else:  # reject
                        # 拒绝售后
                        new_status = 'rejected'
                        order_status = '已收货'  # 恢复原状态

                    # 更新订单状态
                    update_sql = """
                                 UPDATE orders
                                 SET after_sales_status = %s,
                                     status             = %s,
                                     updated_at         = %s
                                 WHERE order_id = %s \
                                 """
                    cursor.execute(update_sql, (new_status, order_status, updated_at, order_id))

                    # 更新售后记录状态
                    update_record_sql = """
                                        UPDATE after_sales_records
                                        SET status       = %s,
                                            processed_at = %s
                                        WHERE order_id = %s
                                          AND status = 'applied' \
                                        """
                    cursor.execute(update_record_sql, (new_status, updated_at, order_id))

                    conn.commit()
                    return True, f"售后请求已{'通过' if action == 'approve' else '拒绝'}"
            except Exception as e:
                conn.rollback()
                print(f"处理售后请求失败: {e}")
                return False, f"处理失败: {str(e)}"